﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
using System.Windows.Forms;
using NutriStat.Data;

namespace NutriStat
{
    /// <summary>
    /// NutriDatabase object. Used for database and table-specific methods, such as table creation.
    /// </summary>
    public class NutriDatabase
    {
        #region Data
        #region Protected Data
        #endregion Protected Data

        #region Private Data
        #endregion Private Data

        #region Internal Data
        #endregion Internal Data
        #endregion Data

        #region Constructors
        #endregion

        #region Properties
        #region Public Properties
        #endregion Public Properties

        #region Protected Properties
        #endregion Protected Properties

        #region Private Properties
        #endregion Private Properties
        #endregion Properties

        #region Methods
        #region Public Methods
        /// <summary>
        /// Creates a new NutriStat data table.
        /// </summary>
        /// <param name="tableName">Name of the table to create</param>
        /// <param name="db">The database inside of which the table will be created</param>
        /// <returns>bool</returns>
        public static bool CreateNutriTable(string tableName, NutriStat.Data.IDbDriver db)
        {
            #region Input Validation
            if (tableName.Length > 255 || tableName.Length <= 0)
            {
                throw new Exception("Parameter out of bounds.");
            }
            #endregion
            try
            {
                List<TableColumn> columns = new List<TableColumn>();
                columns.Add(new TableColumn(NutriStringConstants.ColumnID, GenericDbColumnType.Int32, false));
                columns.Add(new TableColumn(NutriStringConstants.ColumnVisitDate, GenericDbColumnType.Date, false));
                columns.Add(new TableColumn(NutriStringConstants.ColumnCluster, GenericDbColumnType.String, 15, true));
                columns.Add(new TableColumn(NutriStringConstants.ColumnTeam, GenericDbColumnType.String, 15, true));
                columns.Add(new TableColumn(NutriStringConstants.ColumnHousehold, GenericDbColumnType.String, 15, true));
                columns.Add(new TableColumn(NutriStringConstants.ColumnFirstName, GenericDbColumnType.String, 127, true));
                columns.Add(new TableColumn(NutriStringConstants.ColumnLastName, GenericDbColumnType.String, 127, true));
                columns.Add(new TableColumn(NutriStringConstants.ColumnSex, GenericDbColumnType.String, 1, false));
                columns.Add(new TableColumn(NutriStringConstants.ColumnAge, GenericDbColumnType.Single, true));
                columns.Add(new TableColumn(NutriStringConstants.ColumnBirthDate, GenericDbColumnType.Date, true)); // 10
                columns.Add(new TableColumn(NutriStringConstants.ColumnAddress, GenericDbColumnType.String, 127, true));
                columns.Add(new TableColumn(NutriStringConstants.ColumnMother, GenericDbColumnType.String, 127, true));
                columns.Add(new TableColumn(NutriStringConstants.ColumnFather, GenericDbColumnType.String, 127, true)); 
                columns.Add(new TableColumn(NutriStringConstants.ColumnEdema, GenericDbColumnType.Byte, true));
                columns.Add(new TableColumn(NutriStringConstants.ColumnRecumbent, GenericDbColumnType.Byte, true));
                columns.Add(new TableColumn(NutriStringConstants.ColumnNotes, GenericDbColumnType.String, 255, true));
                columns.Add(new TableColumn(NutriStringConstants.ColumnHeightMetric, GenericDbColumnType.Single, true));
                columns.Add(new TableColumn(NutriStringConstants.ColumnHeightCustomary, GenericDbColumnType.Single, true));
                columns.Add(new TableColumn(NutriStringConstants.ColumnWeightMetric, GenericDbColumnType.Single, true));
                columns.Add(new TableColumn(NutriStringConstants.ColumnWeightCustomary, GenericDbColumnType.Single, true)); // 20
                columns.Add(new TableColumn(NutriStringConstants.ColumnWeightOzs, GenericDbColumnType.Single, true));
                columns.Add(new TableColumn(NutriStringConstants.ColumnHeadMetric, GenericDbColumnType.Single, true));
                columns.Add(new TableColumn(NutriStringConstants.ColumnHeadCustomary, GenericDbColumnType.Single, true));
                columns.Add(new TableColumn(NutriStringConstants.ColumnSSFMetric, GenericDbColumnType.Single, true));
                columns.Add(new TableColumn(NutriStringConstants.ColumnSSFCustomary, GenericDbColumnType.Single, true));
                columns.Add(new TableColumn(NutriStringConstants.ColumnTSFMetric, GenericDbColumnType.Single, true));
                columns.Add(new TableColumn(NutriStringConstants.ColumnTSFCustomary, GenericDbColumnType.Single, true));
                columns.Add(new TableColumn(NutriStringConstants.ColumnMUACMetric, GenericDbColumnType.Single, true));
                columns.Add(new TableColumn(NutriStringConstants.ColumnMUACCustomary, GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldCDCHAC", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldCDCWHC", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldCDCWAC", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldCDCHAZ", GenericDbColumnType.Single, true)); 
                columns.Add(new TableColumn("fldCDCWHZ", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldCDCWAZ", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldCDCBMIC", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldCDCBMIZ", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldCDCHCircumC", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldCDCHCircumZ", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldCDCHAFlag", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldCDCWAFlag", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldCDCWHFlag", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldCDCBMIFlag", GenericDbColumnType.Single, true)); 
                columns.Add(new TableColumn("fldCDCFlag", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO1978HAC", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO1978WHC", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO1978WAC", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO1978HAZ", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO1978WHZ", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO1978WAZ", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO1978HAPM", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO1978WHPM", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO1978WAPM", GenericDbColumnType.Single, true)); 
                columns.Add(new TableColumn("fldWHO1978Flag", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldMUACAgeZ", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldMUACHeightZ", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldBMI", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO2006HAC", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO2006WHC", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO2006WAC", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO2006HAZ", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO2006WHZ", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO2006WAZ", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO2006SSFC", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO2006TSFC", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO2006HCircumC", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO2006BMIC", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO2006MUACC", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO2006SSFZ", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO2006TSFZ", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO2006HCircumZ", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO2006BMIZ", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO2006MUACZ", GenericDbColumnType.Single, true)); 
                columns.Add(new TableColumn("fldWHO2006HAFlag", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO2006WAFlag", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO2006WHFlag", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO2006BMIFlag", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO2006HeadFlag", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO2006SSFFlag", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO2006TSFFlag", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO2006MUACFlag", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO2006MMSitting", GenericDbColumnType.Byte, true));
                columns.Add(new TableColumn("fldWHO2006MMStandingAssisted", GenericDbColumnType.Byte, true)); 
                columns.Add(new TableColumn("fldWHO2006MMCrawling", GenericDbColumnType.Byte, true));
                columns.Add(new TableColumn("fldWHO2006MMWalkingAssisted", GenericDbColumnType.Byte, true));
                columns.Add(new TableColumn("fldWHO2006MMStanding", GenericDbColumnType.Byte, true));
                columns.Add(new TableColumn("fldWHO2006MMWalking", GenericDbColumnType.Byte, true));
                columns.Add(new TableColumn("fldWHO2007HAC", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO2007HAZ", GenericDbColumnType.Single, true)); 
                columns.Add(new TableColumn("fldWHO2007WAC", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO2007WAZ", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO2007BMIC", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO2007BMIZ", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO2007HAFlag", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldWHO2007WAFlag", GenericDbColumnType.Single, true));                
                columns.Add(new TableColumn("fldWHO2007BMIFlag", GenericDbColumnType.Single, true));
                columns.Add(new TableColumn("fldCreatedDate", GenericDbColumnType.DateTime, true));
                columns.Add(new TableColumn("fldModifiedDate", GenericDbColumnType.DateTime, true));
                columns.Add(new TableColumn("fldModifiedBy", GenericDbColumnType.String, 127, true));
                db.CreateTable(tableName, columns);
            }
            catch
            {
                return false;
            }

            return true;
        }

        /// <summary>
        /// Converts a database name (e.g. "ACCESS") into a human-readable string (e.g. "Microsoft Access database")
        /// </summary>
        /// <param name="dbType">Name of the database type</param>        
        /// <returns>string</returns>
        internal static string ConvertDbName(string dbType)
        {
            #region Input Validation
            if (dbType.Length > 255 || dbType.Length <= 0)
            {
                throw new Exception("Parameter out of bounds.");
            }
            #endregion

            if (dbType.Equals("ACCESS"))
            {
                return SharedStrings.MICROSOFT_ACCESS_DATABASE;
            }
            else if (dbType.Equals("SQL SERVER"))
            {
                return SharedStrings.MICROSOFT_SQL_SERVER_DATABASE;
            }
            else if (dbType.Equals("MYSQL"))
            {
                return SharedStrings.MYSQL_DATABASE;
            }
            else if (dbType.Equals("ORACLE"))
            {
                return SharedStrings.ORACLE_DATABASE;
            }
            else
            {
                return dbType;
            }
        }

        /// <summary>
        /// Converts a non-NutriStat database into a NutriStat database. The input table is not modified; rather,
        /// a new table is generated with the input table's data using a structure that NutriStat can read.
        /// </summary>
        /// <param name="inputDb">The source database.</param>
        /// <param name="inputDt">The source data table.</param>
        /// <param name="outputDb">The destination database.</param>
        /// <param name="outputTableName">The destination data table name.</param>
        /// <param name="rows">The set of rows, provided by the user, that tells the method how to match columns in each table.</param>
        /// <returns>bool</returns>
        internal static bool ImportTable(IDbDriver inputDb, DataTable inputDt, IDbDriver outputDb, 
            string outputTableName, DataGridViewRowCollection rows, ImportConfiguration importConfig)
        {
            #region Input Validation
            if (outputTableName.Length > 255 || outputTableName.Length <= 0)
            {
                throw new Exception("Parameter out of bounds.");
            }

            if (rows.Count <= 0)
            {
                throw new Exception("Parameter out of bounds.");
            }
            #endregion

            CreateNutriTable(outputTableName, outputDb);
            DataTable outputDt = outputDb.GetTableData(outputTableName, "*", "fldIdNo, fldCurrentDate ASC");            
            
            string userName = System.Security.Principal.WindowsIdentity.GetCurrent().Name.ToString();

            DataTable matchFields = new DataTable("matchFields");
            matchFields.Rows.Add(matchFields.NewRow()); // row 0 = field names in input table
            matchFields.Rows.Add(matchFields.NewRow()); // row 1 = rules for this field
            matchFields.Columns.Add("fldSex");
            matchFields.Columns.Add("fldAge");
            matchFields.Columns.Add("fldBirthDate");
            matchFields.Columns.Add("fldRecumbent");
            matchFields.Columns.Add("fldEdema");
            matchFields.Columns.Add("fldLastName");
            matchFields.Columns.Add("fldFirstName");
            matchFields.Columns.Add("fldCurrentDate");
            matchFields.Columns.Add("fldIdNo");
            matchFields.Columns.Add("fldHeight");
            matchFields.Columns.Add("fldWeight");
            matchFields.Columns.Add("fldHeadCircum");
            matchFields.Columns.Add("fldArmCircum");

            for(int i = 0; i < rows.Count - 1; i++) 
            {                
                if (rows[i].Cells[1].Value.ToString().Equals("Sex")) 
                {                    
                    DataRow row = matchFields.Rows[0];
                    row["fldSex"] = rows[i].Cells[0].Value.ToString();
                    row = matchFields.Rows[1];
                    row["fldSex"] = rows[i].Cells[2].Value.ToString();
                }

                else if (rows[i].Cells[1].Value.ToString().Equals("Age"))
                {                 
                    DataRow row = matchFields.Rows[0];
                    row["fldAge"] = rows[i].Cells[0].Value.ToString();
                    row = matchFields.Rows[1];
                    row["fldAge"] = rows[i].Cells[2].Value.ToString();
                }

                else if (rows[i].Cells[1].Value.ToString().Equals("Birth date"))
                {                    
                    DataRow row = matchFields.Rows[0];
                    row["fldBirthDate"] = rows[i].Cells[0].Value.ToString();                    
                }

                else if (rows[i].Cells[1].Value.ToString().Equals("Recumbent"))
                {                    
                    DataRow row = matchFields.Rows[0];
                    row["fldRecumbent"] = rows[i].Cells[0].Value.ToString();
                    row = matchFields.Rows[1];
                    row["fldRecumbent"] = rows[i].Cells[2].Value.ToString();
                }

                else if (rows[i].Cells[1].Value.ToString().Equals("Edema"))
                {
                    DataRow row = matchFields.Rows[0];
                    row["fldEdema"] = rows[i].Cells[0].Value.ToString();
                    row = matchFields.Rows[1];
                    row["fldEdema"] = rows[i].Cells[2].Value.ToString();
                }

                else if (rows[i].Cells[1].Value.ToString().Equals("Last name"))
                {                    
                    DataRow row = matchFields.Rows[0];
                    row["fldLastName"] = rows[i].Cells[0].Value.ToString();                    
                }

                else if (rows[i].Cells[1].Value.ToString().Equals("First name"))
                {                    
                    DataRow row = matchFields.Rows[0];
                    row["fldFirstName"] = rows[i].Cells[0].Value.ToString();                    
                }

                else if (rows[i].Cells[1].Value.ToString().Equals("Date of measurement"))
                {                    
                    DataRow row = matchFields.Rows[0];
                    row["fldCurrentDate"] = rows[i].Cells[0].Value.ToString();                    
                }

                else if (rows[i].Cells[1].Value.ToString().Equals("ID number"))
                {
                    DataRow row = matchFields.Rows[0];
                    row["fldIdNo"] = rows[i].Cells[0].Value.ToString();
                }

                else if (rows[i].Cells[1].Value.ToString().Equals("Height"))
                {
                    DataRow row = matchFields.Rows[0];
                    row["fldHeight"] = rows[i].Cells[0].Value.ToString();
                    row = matchFields.Rows[1];
                    row["fldHeight"] = rows[i].Cells[2].Value.ToString();
                }

                else if (rows[i].Cells[1].Value.ToString().Equals("Weight"))
                {
                    DataRow row = matchFields.Rows[0];
                    row["fldWeight"] = rows[i].Cells[0].Value.ToString();
                    row = matchFields.Rows[1];
                    row["fldWeight"] = rows[i].Cells[2].Value.ToString();
                }

                else if (rows[i].Cells[1].Value.ToString().Equals("Head circumference"))
                {
                    DataRow row = matchFields.Rows[0];
                    row["fldHeadCircum"] = rows[i].Cells[0].Value.ToString();
                    row = matchFields.Rows[1];
                    row["fldHeadCircum"] = rows[i].Cells[2].Value.ToString();
                }

                else if (rows[i].Cells[1].Value.ToString().Equals("Arm circumference"))
                {
                    DataRow row = matchFields.Rows[0];
                    row["fldArmCircum"] = rows[i].Cells[0].Value.ToString();
                    row = matchFields.Rows[1];
                    row["fldArmCircum"] = rows[i].Cells[2].Value.ToString();
                }
            }

            for (int j = 0; j < inputDt.Rows.Count; j++) 
            {
                Query insertQuery = outputDb.CreateQuery("insert into " + outputTableName + "([" + NutriStringConstants.ColumnLastName + "], [" + NutriStringConstants.ColumnFirstName + "], [fldIdNo], [fldCurrentDate], [fldBirthDate], [fldSex], [fldRecumbent], [fldEdema], [fldAge], [" + NutriStringConstants.ColumnHeightMetric + "],   [" + NutriStringConstants.ColumnHeightCustomary + "], [" + NutriStringConstants.ColumnWeightMetric + "], [" + NutriStringConstants.ColumnWeightCustomary + "],   [" + NutriStringConstants.ColumnHeadMetric + "], [" + NutriStringConstants.ColumnHeadCustomary + "], [" + NutriStringConstants.ColumnMUACMetric + "], [" + NutriStringConstants.ColumnMUACCustomary + "], [fldCreatedDate], [fldModifiedDate], [fldModifiedBy]) " +
                                                                                     "values (@LastName,     @FirstName,     @Id,       @CurrentDate,     @BirthDate,     @Sex,     @Recumbent,     @Edema,     @Age,     @HeightCm,                                           @HeightIn,                                            @WeightKg,                                         @WeightLbs,                                             @HeadCm,        @HeadIn,        @ArmCm,      @ArmIn,     @CreatedDate,     @ModifiedDate,     @ModifiedBy)");
                try
                {
                    // Last name
                    if (matchFields.Rows[0]["fldLastName"].ToString().Equals(string.Empty))
                        insertQuery.Parameters.Add(new QueryParameter("@LastName", DbType.String, string.Empty));
                    else
                        insertQuery.Parameters.Add(new QueryParameter("@LastName", DbType.String, inputDt.Rows[j][matchFields.Rows[0]["fldLastName"].ToString()]));

                    // First name
                    if (matchFields.Rows[0]["fldFirstName"].ToString().Equals(string.Empty))
                        insertQuery.Parameters.Add(new QueryParameter("@FirstName", DbType.String, string.Empty));
                    else
                        insertQuery.Parameters.Add(new QueryParameter("@FirstName", DbType.String, inputDt.Rows[j][matchFields.Rows[0]["fldFirstName"].ToString()]));

                    // ID number
                    if (matchFields.Rows[0]["fldIdNo"].ToString().Equals(string.Empty))
                        insertQuery.Parameters.Add(new QueryParameter("@Id", DbType.Int32, j + 1));
                    else
                        insertQuery.Parameters.Add(new QueryParameter("@Id", DbType.Int32, inputDt.Rows[j][matchFields.Rows[0]["fldIdNo"].ToString()]));

                    // Date of measurement
                    if (inputDt.Rows[j][matchFields.Rows[0]["fldCurrentDate"].ToString()].ToString().Equals(string.Empty))
                        continue;

                    insertQuery.Parameters.Add(new QueryParameter("@CurrentDate", DbType.DateTime, inputDt.Rows[j][matchFields.Rows[0]["fldCurrentDate"].ToString()]));

                    // Birthdate
                    if (matchFields.Rows[0]["fldBirthDate"].ToString().Equals(string.Empty))
                        insertQuery.Parameters.Add(new QueryParameter("@BirthDate", DbType.DateTime, DBNull.Value));
                    else
                        insertQuery.Parameters.Add(new QueryParameter("@BirthDate", DbType.DateTime, inputDt.Rows[j][matchFields.Rows[0]["fldBirthDate"].ToString()]));

                    // Gender
                    if (matchFields.Rows[1]["fldSex"].ToString().Equals("Male/Female"))
                    {
                        if (inputDt.Rows[j][matchFields.Rows[0]["fldSex"].ToString()].ToString().Equals("Male"))
                            insertQuery.Parameters.Add(new QueryParameter("@Sex", DbType.String, "M"));
                        else
                            insertQuery.Parameters.Add(new QueryParameter("@Sex", DbType.String, "F"));
                    }
                    else if (matchFields.Rows[1]["fldSex"].ToString().Equals("1/2"))
                    {
                        if (inputDt.Rows[j][matchFields.Rows[0]["fldSex"].ToString()].ToString().Equals("1"))
                            insertQuery.Parameters.Add(new QueryParameter("@Sex", DbType.String, "M"));
                        else
                            insertQuery.Parameters.Add(new QueryParameter("@Sex", DbType.String, "F"));
                    }
                    else
                    {
                        insertQuery.Parameters.Add(new QueryParameter("@Sex", DbType.String, inputDt.Rows[j][matchFields.Rows[0]["fldSex"].ToString()]));
                    }

                    // Recumbent
                    if (matchFields.Rows[0]["fldRecumbent"].ToString().Equals(string.Empty) || inputDt.Rows[j][matchFields.Rows[0]["fldRecumbent"].ToString()].ToString().Equals(string.Empty))
                    {
                        insertQuery.Parameters.Add(new QueryParameter("@Recumbent", DbType.Int32, DBNull.Value));
                    }
                    else
                    {
                        if (matchFields.Rows[1]["fldRecumbent"].ToString().Equals("1/0"))
                        {
                            insertQuery.Parameters.Add(new QueryParameter("@Recumbent", DbType.Int32, inputDt.Rows[j][matchFields.Rows[0]["fldRecumbent"].ToString()]));
                        }
                        else if (matchFields.Rows[1]["fldRecumbent"].ToString().Equals("Y/N"))
                        {
                            if (inputDt.Rows[j][matchFields.Rows[0]["fldRecumbent"].ToString()].ToString().Equals("Y"))
                                insertQuery.Parameters.Add(new QueryParameter("@Recumbent", DbType.Int32, 1));
                            else if (inputDt.Rows[j][matchFields.Rows[0]["fldRecumbent"].ToString()].ToString().Equals("N"))
                                insertQuery.Parameters.Add(new QueryParameter("@Recumbent", DbType.Int32, 0));
                            else
                                insertQuery.Parameters.Add(new QueryParameter("@Recumbent", DbType.Int32, DBNull.Value));
                        }
                        else if (matchFields.Rows[1]["fldRecumbent"].ToString().Equals("Yes/No"))
                        {
                            if (inputDt.Rows[j][matchFields.Rows[0]["fldRecumbent"].ToString()].ToString().Equals("Yes"))
                                insertQuery.Parameters.Add(new QueryParameter("@Recumbent", DbType.Int32, 1));
                            else if (inputDt.Rows[j][matchFields.Rows[0]["fldRecumbent"].ToString()].ToString().Equals("No"))
                                insertQuery.Parameters.Add(new QueryParameter("@Recumbent", DbType.Int32, 0));
                            else
                                insertQuery.Parameters.Add(new QueryParameter("@Recumbent", DbType.Int32, DBNull.Value));
                        }
                        else
                        {
                            insertQuery.Parameters.Add(new QueryParameter("@Recumbent", DbType.Int32, DBNull.Value));
                        }
                    }

                    // Edema
                    if (matchFields.Rows[0]["fldEdema"].ToString().Equals(string.Empty) || inputDt.Rows[j][matchFields.Rows[0]["fldEdema"].ToString()].ToString().Equals(string.Empty))
                    {
                        insertQuery.Parameters.Add(new QueryParameter("@Edema", DbType.Int32, DBNull.Value));
                    }
                    else
                    {
                        if (matchFields.Rows[1]["fldEdema"].ToString().Equals("1/0"))
                        {
                            insertQuery.Parameters.Add(new QueryParameter("@Edema", DbType.Int32, inputDt.Rows[j][matchFields.Rows[0]["fldEdema"].ToString()]));
                        }
                        else if (matchFields.Rows[1]["fldEdema"].ToString().Equals("Y/N"))
                        {
                            if (inputDt.Rows[j][matchFields.Rows[0]["fldEdema"].ToString()].ToString().Equals("Y"))
                                insertQuery.Parameters.Add(new QueryParameter("@Edema", DbType.Int32, 1));
                            else if (inputDt.Rows[j][matchFields.Rows[0]["fldEdema"].ToString()].ToString().Equals("N"))
                                insertQuery.Parameters.Add(new QueryParameter("@Edema", DbType.Int32, 0));
                            else
                                insertQuery.Parameters.Add(new QueryParameter("@Edema", DbType.Int32, DBNull.Value));
                        }
                        else if (matchFields.Rows[1]["fldEdema"].ToString().Equals("Yes/No"))
                        {
                            if (inputDt.Rows[j][matchFields.Rows[0]["fldEdema"].ToString()].ToString().Equals("Yes"))
                                insertQuery.Parameters.Add(new QueryParameter("@Edema", DbType.Int32, 1));
                            else if (inputDt.Rows[j][matchFields.Rows[0]["fldEdema"].ToString()].ToString().Equals("No"))
                                insertQuery.Parameters.Add(new QueryParameter("@Edema", DbType.Int32, 0));
                            else
                                insertQuery.Parameters.Add(new QueryParameter("@Edema", DbType.Int32, DBNull.Value));
                        }
                        else
                        {
                            insertQuery.Parameters.Add(new QueryParameter("@Edema", DbType.Int32, DBNull.Value));
                        }
                    }

                    // Age
                    if (matchFields.Rows[0]["fldAge"].ToString().Equals(string.Empty))
                    {
                        insertQuery.Parameters.Add(new QueryParameter("@Age", DbType.Single, DBNull.Value));
                    }
                    else
                    {
                        string age = inputDt.Rows[j][matchFields.Rows[0]["fldAge"].ToString()].ToString();
                        double ageD = Convert.ToSingle(age);
                        if (matchFields.Rows[0]["fldAge"].ToString().Equals(SharedStrings._YEARS))
                            insertQuery.Parameters.Add(new QueryParameter("@Age", DbType.Single, ageD * 12));
                        else if (matchFields.Rows[0]["fldAge"].ToString().Equals(SharedStrings._DAYS))
                            insertQuery.Parameters.Add(new QueryParameter("@Age", DbType.Single, ageD / 30.25));
                        else if (matchFields.Rows[0]["fldAge"].ToString().Equals(SharedStrings._MONTHS))
                            insertQuery.Parameters.Add(new QueryParameter("@Age", DbType.Single, ageD));
                        else
                            insertQuery.Parameters.Add(new QueryParameter("@Age", DbType.Single, DBNull.Value));
                    }

                    // Height
                    if (matchFields.Rows[0]["fldHeight"].ToString().Equals(string.Empty) || inputDt.Rows[j][matchFields.Rows[0]["fldHeight"].ToString()].ToString().Equals(string.Empty))
                    {
                        insertQuery.Parameters.Add(new QueryParameter("@HeightCm", DbType.Single, DBNull.Value));
                        insertQuery.Parameters.Add(new QueryParameter("@HeightIn", DbType.Single, DBNull.Value));
                    }
                    else
                    {
                        double height = Convert.ToDouble(inputDt.Rows[j][matchFields.Rows[0]["fldHeight"].ToString()]);

                        if (matchFields.Rows[1]["fldHeight"].ToString().Equals(MeasurementUnits.Centimeter.Abbreviation))
                        {
                            insertQuery.Parameters.Add(new QueryParameter("@HeightCm", DbType.Single, height));
                            insertQuery.Parameters.Add(new QueryParameter("@HeightIn", DbType.Single, height * MeasurementUnits.Centimeter.ToInches()));
                        }
                        else
                        {
                            insertQuery.Parameters.Add(new QueryParameter("@HeightCm", DbType.Single, height * MeasurementUnits.Inch.ToCentimeters()));
                            insertQuery.Parameters.Add(new QueryParameter("@HeightIn", DbType.Single, height));
                        }
                    }

                    // Weight
                    if (matchFields.Rows[0]["fldWeight"].ToString().Equals(string.Empty) || inputDt.Rows[j][matchFields.Rows[0]["fldWeight"].ToString()].ToString().Equals(string.Empty))
                    {
                        insertQuery.Parameters.Add(new QueryParameter("@WeightKg", DbType.Single, DBNull.Value));
                        insertQuery.Parameters.Add(new QueryParameter("@WeightLb", DbType.Single, DBNull.Value));
                    }
                    else
                    {
                        double weight = Convert.ToDouble(inputDt.Rows[j][matchFields.Rows[0]["fldWeight"].ToString()]);

                        if (matchFields.Rows[1]["fldWeight"].ToString().Equals(MeasurementUnits.Kilogram.Abbreviation))
                        {
                            insertQuery.Parameters.Add(new QueryParameter("@WeightKg", DbType.Single, weight));
                            insertQuery.Parameters.Add(new QueryParameter("@WeightLb", DbType.Single, weight * MeasurementUnits.Kilogram.ToPounds()));
                        }
                        else
                        {
                            insertQuery.Parameters.Add(new QueryParameter("@WeightKg", DbType.Single, weight * MeasurementUnits.Pound.ToKilograms()));
                            insertQuery.Parameters.Add(new QueryParameter("@WeightLb", DbType.Single, weight));
                        }
                    }

                    // Head circumference
                    if (matchFields.Rows[0]["fldHeadCircum"].ToString().Equals(string.Empty) || inputDt.Rows[j][matchFields.Rows[0]["fldHeadCircum"].ToString()].ToString().Equals(string.Empty))
                    {
                        insertQuery.Parameters.Add(new QueryParameter("@HeadCm", DbType.Single, DBNull.Value));
                        insertQuery.Parameters.Add(new QueryParameter("@HeadIn", DbType.Single, DBNull.Value));
                    }
                    else
                    {
                        double head = Convert.ToDouble(inputDt.Rows[j][matchFields.Rows[0]["fldHeadCircum"].ToString()]);

                        if (matchFields.Rows[1]["fldHeadCircum"].ToString().Equals(MeasurementUnits.Centimeter.Abbreviation))
                        {
                            insertQuery.Parameters.Add(new QueryParameter("@HeadCm", DbType.Single, head));
                            insertQuery.Parameters.Add(new QueryParameter("@HeadIn", DbType.Single, head * MeasurementUnits.Centimeter.ToInches()));
                        }
                        else
                        {
                            insertQuery.Parameters.Add(new QueryParameter("@HeadCm", DbType.Single, head * MeasurementUnits.Inch.ToCentimeters()));
                            insertQuery.Parameters.Add(new QueryParameter("@HeadIn", DbType.Single, head));
                        }
                    }

                    // Mid-upper arm circumference
                    if (matchFields.Rows[0]["fldArmCircum"].ToString().Equals(string.Empty) || inputDt.Rows[j][matchFields.Rows[0]["fldArmCircum"].ToString()].ToString().Equals(string.Empty))
                    {
                        insertQuery.Parameters.Add(new QueryParameter("@ArmCm", DbType.Single, DBNull.Value));
                        insertQuery.Parameters.Add(new QueryParameter("@ArmIn", DbType.Single, DBNull.Value));
                    }
                    else
                    {
                        double arm = Convert.ToDouble(inputDt.Rows[j][matchFields.Rows[0]["fldArmCircum"].ToString()]);

                        if (matchFields.Rows[1]["fldArmCircum"].ToString().Equals(MeasurementUnits.Centimeter.Abbreviation))
                        {
                            insertQuery.Parameters.Add(new QueryParameter("@ArmCm", DbType.Single, arm));
                            insertQuery.Parameters.Add(new QueryParameter("@ArmIn", DbType.Single, arm * MeasurementUnits.Centimeter.ToInches()));
                        }
                        else
                        {
                            insertQuery.Parameters.Add(new QueryParameter("@ArmCm", DbType.Single, arm * MeasurementUnits.Inch.ToCentimeters()));
                            insertQuery.Parameters.Add(new QueryParameter("@ArmIn", DbType.Single, arm));
                        }
                    }


                    insertQuery.Parameters.Add(new QueryParameter("@CreatedDate", DbType.String, System.DateTime.Now));
                    insertQuery.Parameters.Add(new QueryParameter("@ModifiedDate", DbType.String, System.DateTime.Now));
                    insertQuery.Parameters.Add(new QueryParameter("@ModifiedBy", DbType.String, userName));
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, SharedStrings.ERROR, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return false;
                }

                outputDb.ExecuteNonQuery(insertQuery);
            }           
            
            outputDb.Dispose();
            outputDt.Dispose();
            inputDb.Dispose();
            inputDt.Dispose();            

            return true;
        }
        #endregion Public Methods

        #region Protected Methods
        #endregion Protected Methods

        #region Private Methods
        #endregion Private Methods
        #endregion Methods
    }
}
